/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ve.usb.cep.db.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ve.usb.cep.db.SelectObject;
import ve.usb.cep.db.SelectException;
import ve.usb.cep.db.Session;
import ve.usb.cep.db.domainObjects.Exonerado;
import ve.usb.cep.db.modelExceptions.ExoneradoException;
import ve.usb.cep.util.sqlWhere;

/**
 *
 * @author lucia
 */
public class ExoneradoModel {
    Connection conn;
    PreparedStatement rowCountStatement;

    public Exonerado set(Exonerado exo)
        throws ExoneradoException
    {
        if (0 == exo.getCodigo()){
            add(exo);
        }else{
            update(exo);
        }

        return exo;
    }
    
    public Exonerado add(Exonerado exonerado)
        throws ExoneradoException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO exonerado " +
                "(estudiante, oferta)" +
                " VALUES (?, ?)");


            pstmt.setInt(1, exonerado.getEstudiante());
            pstmt.setInt(2, exonerado.getOferta());
            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }
        return exonerado;
    }
    
    public Exonerado update(Exonerado exonerado)
        throws ExoneradoException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("UPDATE exonerado " +
              "SET estudiante=?, oferta=?" +
              " WHERE codigo=?");

            pstmt.setInt(1, exonerado.getEstudiante());
            pstmt.setInt(2, exonerado.getOferta());
            pstmt.setInt(3, exonerado.getCodigo());
            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }
        return exonerado;
    }

    public void delete(int codigo)
        throws ExoneradoException
    {
        conn = Session.connection();
        try{
            PreparedStatement p = conn.prepareStatement("DELETE FROM exonerado WHERE codigo=?");
            p.setInt(1, codigo);
            p.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }
    }

    protected Exonerado getByField(String field, String value)
       throws ExoneradoException
    {
        sqlWhere w = new sqlWhere();
        sqlWhere.rule[] c = new sqlWhere.rule[1];
        c[0] = w.new rule();
        c[0].setField(field);
        c[0].setOp("eq");
        c[0].setData(value);
        w.setGroupOp(" AND ");
        w.setRules(c);
        
        ArrayList<Exonerado> u = getCustom(new SelectObject((short) 1, (short) 1, "", "", w));
        if (0 == u.size()){
            return null;
        }else{
            return u.get(0);
        }
    }
    
    public ArrayList<Exonerado> getCustom(SelectObject s) 
            throws ExoneradoException {

        conn = Session.connection();
        s.setConnection(conn);
        s.setFields(Exonerado.getFields());
        s.setTypes(Exonerado.getTypes());
        s.setTable("exonerado");

        PreparedStatement p;
        try{
            p = s.getSearchQuery();
            rowCountStatement = s.getCountQuery();
        }catch(SelectException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }

        ArrayList<Exonerado> arr = new ArrayList<Exonerado>();
        try{
            ResultSet rs = p.executeQuery();
            while (rs.next()){
                arr.add(new Exonerado(
                    rs.getInt("codigo"),
                    rs.getInt("estudiante"),
                    rs.getInt("oferta")
                ));
            }
        }catch(SQLException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }

        return arr;
    }

    public int getRowCount()
        throws ExoneradoException
    {
        try{
            ResultSet rs = rowCountStatement.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(SQLException e){
            e.printStackTrace();
            throw new ExoneradoException(e.getMessage());
        }
    }
}
